import os
import csv
import sys
import datetime
from odps import ODPS
"""
导出库存
"""


def get_yesterday(day_num=1, day_format="%Y-%m-%d"):
    today = datetime.date.today()
    oneday = datetime.timedelta(days=day_num)
    yesterday = today - oneday
    return yesterday.strftime(day_format)


if __name__ == '__main__':
    try:
        day_interval = int(sys.argv[1])
    except:
        day_interval = 1
    if day_interval < 1:
        day_interval = 1
    # 注意：时间库存这里取 T+2, 因为T+1当天手艺人已开始上门，进而会发生加单行为
    select_day = get_yesterday(day_interval + 1, '%Y%m%d')
    save_day = get_yesterday(day_interval)
    target_dir = "/data/disp/" + save_day + "/"
    try:
        os.makedirs(target_dir)
    except:
        pass
    target_file = target_dir + "inventory.csv"
    if not os.path.exists(target_file):
        # 这里替换成你自己的存储连接
        odps = ODPS('xxx', 'xxxxxxxxxxx', 'XXX_APP', endpoint='http://service.odps.aliyun.com/api')
        inventory_sql = """
        SELECT scope_id as artisan_id
                ,case SUBSTR(value0,31,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 00_00
                ,case SUBSTR(value0,29,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 00_30
                ,case SUBSTR(value0,27,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 01_00
                ,case SUBSTR(value0,25,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 01_30
                ,case SUBSTR(value0,23,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 02_00
                ,case SUBSTR(value0,21,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 02_30
                ,case SUBSTR(value0,19,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 03_00
                ,case SUBSTR(value0,17,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 03_30
                ,case SUBSTR(value0,15,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 04_00
                ,case SUBSTR(value0,13,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 04_30
                ,case SUBSTR(value0,11,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 05_00
                ,case SUBSTR(value0,9,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 05_30
                ,case SUBSTR(value0,7,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 06_00
                ,case SUBSTR(value0,5,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 06_30
                ,case SUBSTR(value0,3,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 07_00
                ,case SUBSTR(value0,1,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 07_30
                ,case SUBSTR(value1,31,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 08_00
                ,case SUBSTR(value1,29,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 08_30
                ,case SUBSTR(value1,27,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 09_00
                ,case SUBSTR(value1,25,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 09_30
                ,case SUBSTR(value1,23,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 10_00
                ,case SUBSTR(value1,21,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 10_30
                ,case SUBSTR(value1,19,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 11_00
                ,case SUBSTR(value1,17,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 11_30
                ,case SUBSTR(value1,15,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 12_00
                ,case SUBSTR(value1,13,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 12_30
                ,case SUBSTR(value1,11,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 13_00
                ,case SUBSTR(value1,9,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 13_30
                ,case SUBSTR(value1,7,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 14_00
                ,case SUBSTR(value1,5,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 14_30
                ,case SUBSTR(value1,3,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 15_00
                ,case SUBSTR(value1,1,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 15_30
                ,case SUBSTR(value2,31,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 16_00
                ,case SUBSTR(value2,29,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 16_30
                ,case SUBSTR(value2,27,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 17_00
                ,case SUBSTR(value2,25,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 17_30
                ,case SUBSTR(value2,23,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 18_00
                ,case SUBSTR(value2,21,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 18_30
                ,case SUBSTR(value2,19,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 19_00
                ,case SUBSTR(value2,17,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 19_30
                ,case SUBSTR(value2,15,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 20_00
                ,case SUBSTR(value2,13,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 20_30
                ,case SUBSTR(value2,11,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 21_00
                ,case SUBSTR(value2,9,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 21_30
                ,case SUBSTR(value2,7,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 22_00
                ,case SUBSTR(value2,5,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 22_30
                ,case SUBSTR(value2,3,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 23_00
                ,case SUBSTR(value2,1,2) when '00' then -1 when '01' then -1 when '10' then 0 else 1 end as 23_30
        FROM xxx_ods.dw_ar_inventory_time
        WHERE ds='%s' AND SUBSTR(date_tm,1,10)='%s'
         AND scope='1' AND unit='2'
        """ % (select_day, save_day)

        with odps.execute_sql(inventory_sql).open_reader() as reader:
            result_df = reader.to_pandas()

        if result_df is not None and len(result_df) > 0:
            result_df.to_csv(target_file, quoting=csv.QUOTE_ALL)
            print('export inventory:' + str(len(result_df)))
